Bentley OpenUtilities CONNECT Edition Help

Creating the Oracle Spatial Schema

Most scripts required to create an Oracle Spatial database are automatically created by macros in the excel sheet BUDOracleSpatialTools.xlsm. , and the set of Model Administrator/Data Loader spreadsheets for each commodity (_BentleyElectric_DL.xls, _BentleyGas_DL.xls, _BentleyWater_DL.xls, _BentleyWastewater_DL.xls). In addition, spatial scripts are generated by the DGN2SDO.exe application. Only one script must be manually created.

Working folders

To begin the process of creating scripts you must create a working folder.

To create a working folder :

  1. Create a working folder such as “d:\BUDOS”. The folder is mandatory, but can be located and named to your preferences.
  2. Copy the workbook BUDOracleSpatialToolsv1.xlsm into your working folder. The file is located in the unpacked BentleyDownloads folder for the product. The default path to the workbook is “C:\BentleyDownloads\bud081109XXen\Database\Oracle”.
  3. Create the subfolder OSSchemaAdditions. The folder is mandatory.

BUDOracleSpatialTools.xlsm

The BUDOracleSpatialTools workbook is used to automate generation of most of the command files, batch files, and supporting SQL scripts needed to prepare an Oracle Spatial environment.

The Settings worksheet in the BUDOracleSpatialToolsv workbook is used to set the parameters for the database environment and the folders characteristic for the installation of the database server and the installation of the product used for the environment set up.

To generate scripts from BUDOracleSpatialTools :

  1. Open the workbook.
  2. Navigate to the Settings tab.
  3. At a minimum change the following settings for your environment:
    • Output Directory – the location for the scripts that are generated
    • Database name (SID) – the SID or Service Name for the database
    • Base directory for the Oracle data files – the path to the tablespaces for the Oracle server, e.g. “D:\oracle\database\BUDGIS\”
    • User Passwords – passwords for sys, system, and default schema users
    • OUT_CS – the coordinate system for the spatial environment
    • OUT_XDIM – the minimum and maximum X coordinate extents.
    • OUT_YDIM – the minimum and maximum Y coordinate extents.
  4. Run the two macros PreMasterLoad and PostMasterLoad from the Microsoft Excel Macros Dialog after configuring the environment settings.
    PreMasterLoad

    Generates command files, batch files, and SQL script files to prepare the database for a run of the DGN2SDO application. The PreMasterLoad macro creates these scripts:

    • CreateTablesspaces.cmd
    • CreateTablespaces.sql
    • CreateRolesAndUsers.cmd
    • CreateRolesAndUsers.sql
    • BUD_DGN2SDO.bat
    • RunMasterLoads.cmd
    PostMasterLoad

    Generates command files and SQL script files to complete the database preparation after having run the DGN2SDO SQL scripts. The PostMasterLoad macro creates these scripts:

    • BUD_TABLEVIEWGRANTSTOROLES.cmd
    • Generate_BUD_TABLEVIEWGRANTSTOROLES.sql
    • CreateVersioning.cmd, CreateVersioning.sql
    • GenerateVersioning_BUDMISC.sql
    • GenerateVersioning_BUDELEC.sql
    • GenerateVersioning_BUDGAS.sql
    • GenerateVersioning_BUDWATER.sql
    • GenerateVersioning_BUDWWATER.sql
    • /OSSchemaAdditions/SchemaAdditions.cmd

Model Administrator/Data Loaders

The Model Administrator (MA)/Data Loader (DL) are the set of worksheets within a set of workbooks (one for each commodity) used to provision a configuration for Bentley OpenUtilities Designer. The workbooks store metadata about each commodity data model and provide macros used to generate XFM schema files and supporting XML files. MA worksheets contain the Feature and Network definitions. DL is composed of all the other worksheets for other parts of the application such as Workflow Manager, Units Manager, and so forth. The Overview tab provides links to navigate to each MA worksheet in the section labeled Model Administrator. The workbooks are typically found in the DataModel folder of the product install, "C:\Program Files (x86)\Bentley\UtilitiesDesigner\UtilitiesDesigner\DataModel\".

To generate scripts from _BentleyElectric_DL:

  1. Open the workbook.
  2. Navigate to the OracleSpatialSettings tab.
  3. Change the parameters required to successfully connect to the spatial database.
  4. Execute the macro CreateOracleSpatialSchema from the Macro Dialog to generate the BUDElectric_OSAdditions.sql script file. The script adds tables to support relationships.
  5. Execute the macro CreateOSTextAttributeScript from the Macro Dialog to generate the BUDElectric_OSTextAttrs.sql script file. The script contains Alter Table statements to insert rotation and x y columns to point features.
  6. Manually remove DESIGNWORKINGLOCATIONTEXT1 ALTER TABLE statements from BUDElectric_OSTextAttrs.sql.
  7. Move BUDElectric_OSTextAttrs.sql into the OSSchemaAdditions subfolder.

To generate scripts from _BentleyGas_DL:

  1. Open the workbook.
  2. Navigate to the OracleSpatialSettings tab.
  3. Change the parameters required to successfully connect to the spatial database.
  4. Execute the macro CreateOracleSpatialSchema from the Macro Dialog to generate the BUDGas_OSAdditions.sql script file. The script adds tables to support relationships.
  5. Execute the macro CreateOSTextAttributeScript from the Macro Dialog to generate the BUDElectric_OSTextAttrs.sql script file. The script contains Alter Table statements to insert rotation and x y columns to point features.
  6. Manually remove DESIGNWORKINGLOCATIONTEXT1 ALTER TABLE statements from BUDGas_OSTextAttrs.sql.
  7. Move BUDGas_OSTextAttrs.sql into the OSSchemaAdditions subfolder.

To generate scripts from _BentleyWater_DL:

  1. Open the workbook.
  2. Navigate to the OracleSpatialSettings tab.
  3. Change the parameters required to successfully connect to the spatial database.
  4. Execute the macro CreateOracleSpatialSchema from the Macro Dialog to generate the BUDWater_OSAdditions.sql script file. The script adds tables to support relationships.
  5. Execute the macro CreateOSTextAttributeScript from the Macro Dialog to generate the BUDWater_OSTextAttrs.sql script file. The script contains Alter Table statements to insert rotation and x y columns to point features.
  6. Manually remove DESIGNWORKINGLOCATIONTEXT1 ALTER TABLE statements from BUDWater_OSTextAttrs.sql.
  7. Move BUDWater_OSTextAttrs.sql into the OSSchemaAdditions subfolder.

To generate scripts from _BentleyWastewater_DL:

  1. Open the workbook.
  2. Navigate to the OracleSpatialSettings tab.
  3. Change the parameters required to successfully connect to the spatial database.
  4. Execute the macro CreateOracleSpatialSchema from the Macro Dialog to generate the BUDWastewater_OSAdditions.sql script file. The script adds tables to support relationships.
  5. Execute the macro CreateOSTextAttributeScript from the Macro Dialog to generate the BUDWastewater_OSTextAttrs.sql script file. The script contains Alter Table statements to insert rotation and x y columns to point features.
  6. Manually remove DESIGNWORKINGLOCATIONTEXT1 ALTER TABLE statements from BUDWastewater_OSTextAttrs.sql.
  7. Move BUDWastewater_OSTextAttrs.sql into the OSSchemaAdditions subfolder.

BUDMISC_OSTextAttrs.sql

The script BUDMISC_OSTextAttrs.sql is not currently auto-generated.

To create the BUDMISC_OSTextAttrs.sql script:

  1. Navigate to the OSSchemaAdditions folder.
  2. Manually create the script file BUDMISC_OSTextAttrs.sql.
  3. Add the statements below to the script. Substitute your database parameters in the connect statement "connect sys/bentley@BUDGIS;". set echo on; spool 'BUDMISC_OSTextAttrs.log'; connect sys/bentley@BUDGIS; ALTER TABLE BUDMISC.DESIGNWORKLOCATIONText1 ADD (MS_ANGLE NUMBER); ALTER TABLE BUDMISC.DESIGNWORKLOCATIONText1 ADD (MS_X_SCALE NUMBER); ALTER TABLE BUDMISC.DESIGNWORKLOCATIONText1 ADD (MS_Y_SCALE NUMBER); COMMIT; spool off; exit;
  4. Save the file.

Execute the scripts

It is recommended that scripts are run from the command prompt. Navigate to each folder as required using the "CD" command, e.g. "cd D:\BUDOS\ OSSchemaAdditions".

Prepare the database for DGN2SDO

To create the Tablespaces :

  1. Navigate to the working folder root, e.g. "d:>cd d:\budos".
  2. Execute the CreateTablespaces.cmd, e.g. "d:\budos> CreateTablespaces.cmd".
  3. Check the CreateTablespaces.log file for errors.

To create Roles and Users :

  1. Execute the CreateRolesAndUsers.cmd.
  2. Check the CreateRolesAndUsers.log for errors.

DGN2SDO

The DGN2SDO process generates sql scripts for each commodity into the subfolders DGN2SDO_BUDELECTRIC, DGN2SDO_BUDGAS, BUD2SDO_BUDWATER, DGN2SDO_BUDWWATER, and DGN2SDO_BUDMISC.

To run DGN2SDO :

  1. Navigate to the working folder root, e.g. “d:>cd d:\budos”.
  2. Run the batch file, BUD_DGN2SDO.bat.

To prepare to execute the DGN2SDO generated scripts

  1. Navigate into the DGN2SDO_BUDELECTRIC folder.
  2. Edit MasterLoad.bat batch file.
  3. Comment out domain tables.
  4. Navigate into the DGN2SDO_BUDGAS folder.
  5. Edit MasterLoad.bat batch file.
  6. Comment out domain tables.
  7. Navigate into the DGN2SDO_ BUDWATER folder.
  8. Edit MasterLoad.bat batch file.
  9. Comment out domain tables.
  10. Navigate into the DGN2SDO_ BUDWWATER folder.
  11. Edit MasterLoad.bat batch file.
  12. Comment out domain tables.
  13. Navigate into the DGN2SDO_ DGN2SDO_BUDMISC folder.
  14. Edit MasterLoad.bat batch file.
  15. Comment out domain tables.

To create the tables in the database :

  1. Navigate into the root working folder, e.g. “d:\BUDOS”.
  2. Execute the MasterLoads.cmd command file. This process may take 10 minutes or more depending on the specific server environement.
  3. Navigate into the DGN2SDO_BUDELEC subfolder.
  4. Check the MasterLoad.log file for errors.
  5. Navigate into the DGN2SDO_BUDGAS subfolder.
  6. Check the MasterLoad.log file for errors.
  7. Navigate into the DGN2SDO_BUDWATER subfolder.
  8. Check the MasterLoad.log file for errors.
  9. Navigate into the DGN2SDO_BUDWWATER subfoler.
  10. Check MasterLoad.log file for errors.
  11. Navigate into DGN2SDO_BUDMISC subfolder.
  12. Check the MasterLoad.log file for errors.

Post DGN2SDO tasks

To create the relationship tables :

  1. Navigate into the root working folder, e.g. “d:\budos”.
  2. Execute the SchemaAdditions.cmd command file.
  3. Check log files in C:\BUDOS\ for errors.

To version enable the database :

This is an optional task. Perform these steps only if versioning is required.

  1. Navigate into the root working folder, e.g. “d:\budos”.
  2. Execute the CreateVersioning.cmd.
  3. Check the various “EnableVersioning_*.log” files for errors.

To set grants to roles in the database :

  1. Navigate into the root working folder, e.g. “d”\budos”.
  2. Execute the BUD_TABLEVIEWGRANTSTOROLES.cmd.
  3. Check the Make_BUD_TABLEVIEWGRANTSTOROLES.log file for errors.
  4. Restart the Oracle database.
Parent topic: Setting Up Oracle Spatial 2-tier